IF exists(SELECT * FROM dbo.sysobjects WHERE name='controleRoosterOpdrachten' AND xtype='P') 
BEGIN
	DROP PROCEDURE controleRoosterOpdrachten
END
GO

create procedure controleRoosterOpdrachten as
set nocount on

create table #fouten(
naamcode char(5),
vak char(2),
klas varchar(20),
aardFouten varchar(20)
)
insert into #fouten(naamcode,vak,klas,aardFouten)
select distinct o.naamcode,o.vakCode,o.klas,'Enkel in opdrachten' 
from ambtsverdeling as o
  left join rooster as r
  on r.deLeraar = o.naamcode and r.deKlas = o.klas and r.hetVak = o.vakCode
where r.deLeraar is null 

insert into #fouten(naamcode,vak,klas,aardFouten)
select distinct r.deLeraar,r.hetVak,r.deKlas,'Enkel in rooster' 
from rooster as r
  left join ambtsverdeling as o
  on r.deLeraar = o.naamcode and r.deKlas = o.klas and r.hetVak = o.vakCode
where o.naamcode is null

select * from #fouten order by aardFouten,vak,naamcode,klas